﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using IBM.Data.DB2;
using DBService;
using System.Collections;

namespace DB2Service
{
    public class DB2ServiceClass:IDBService
    {
        #region IDBService 成员

        System.Data.DataTable IDBService.Select(string sql, string conectionString)
        {
            DataTable dt = new DataTable();
            DB2Command cmd = new DB2Command(sql, new DB2Connection(conectionString));
            DB2DataAdapter adt = new DB2DataAdapter(cmd);
            adt.Fill(dt);
            cmd.Connection.Close();
            return dt;
        }

        object IDBService.ExcecuteScalar(string sql, string conectionString)
        {

            DB2Command cmd = new DB2Command(sql, new DB2Connection(conectionString));
            cmd.Connection.Open();
            object bb = cmd.ExecuteScalar();
            cmd.Connection.Close();
            cmd.Dispose();
            return bb;
        }
       
        int IDBService.ExecuteNonQuery(string sql, string conectionString)
        {
            DB2Command cmd = new DB2Command(sql, new DB2Connection(conectionString));
           
            cmd.Connection.Open();
            int bb = cmd.ExecuteNonQuery();
            cmd.Connection.Close();
            cmd.Dispose();
            return bb;
        }
       public int ExecuteNonQuery(string sql, string conectionString,Hashtable column_value)
        {
            DB2Command cmd = new DB2Command(sql, new DB2Connection(conectionString));
            getParameters(cmd.Parameters,column_value);
            cmd.Connection.Open();
            int bb = cmd.ExecuteNonQuery();
            cmd.Connection.Close();
            cmd.Dispose();
            return bb;
        }
         public int ExecuteNonQuery(string sql, string conectionString,DBParameterCollentions paras)
        {
            DB2Command cmd = new DB2Command(sql, new DB2Connection(conectionString));
            getParameters(cmd.Parameters, paras);
            cmd.Connection.Open();
            int bb = cmd.ExecuteNonQuery();
            cmd.Connection.Close();
            cmd.Dispose();
            return bb;
        }
     

        public object ExcecuteScalar(string sql, string conectionString, Hashtable column_value)
        {

            DB2Command cmd = new DB2Command(sql, new DB2Connection(conectionString));
            getParameters(cmd.Parameters, column_value);
            cmd.Connection.Open();
            object bb = cmd.ExecuteScalar();
            cmd.Connection.Close();
            cmd.Dispose();
            return bb;
        }
      

      

        string IDBService.ToPaginatedSql(string sql, long rows, long pageIndex)
        {
            string selectString=sql;
            long page = pageIndex;
            string pageStr = "";
            string rownumberColumnName = "myrowsx";
            if (page != 0)
            {

                pageStr = string.Format("and ({2}>{0} and {2}<={1})", rows * (page - 1), rows * page, rownumberColumnName);
            }
            string temp = selectString.ToLower();

            int index = temp.IndexOf("select") + 6;
            temp = selectString.Substring(index, selectString.Length - index);
            return string.Format("select * from(select (ROW_NUMBER() OVER({3})) as {0} , {1} )cc where 1=1 {2}", rownumberColumnName, temp, pageStr, getOrderCondition(temp));
        }
        public  string getOrderCondition(string selectStr)
        {
            string temp = selectStr.ToLower();
            int index = temp.IndexOf("order");
            if (index < 0) return "";
            return selectStr.Substring(index, selectStr.Length - index);
        }

        string IDBService.ToCountedSql(string sql)
        {
            /*
            string selectString = sql;
            string temp = selectString.ToLower();

            int index = temp.IndexOf("from") + 4;

            selectString = selectString.Substring(index, selectString.Length - (index + 1));
            temp = selectString.ToLower();
            index = temp.IndexOf("order");
            if (index != -1) selectString = selectString.Substring(0, index);

            return string.Format("select count(*) from {0} ", selectString);
              */
            return string.Format("select count(*) from ({0}) as mycounttable ", sql);
        }
        DataTable IDBService.ExcecuteStoredProcedure(string conectionString,string procedure_name, DBParameterCollentions paras)
        {
            DataTable dt = new DataTable();
            DB2Command cmd = new DB2Command();
            cmd.Connection = new DB2Connection(conectionString);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = procedure_name;
            getParameters(cmd.Parameters, paras);

            DB2DataAdapter adt = new DB2DataAdapter(cmd);
            adt.Fill(dt);
            return dt;

        }
        #endregion
        private void getParameters(DB2ParameterCollection paras, Hashtable column_value)
        {

            foreach (DictionaryEntry v in column_value)
            {
                paras.Add("@" + v.Key.ToString(), v.Value);
            }


        }
        private void getParameters(DB2ParameterCollection paras, DBParameterCollentions paras2)
        {
            DB2Parameter p;
           
         
                foreach (DBParameter v in paras2)
                {
                    p = paras.Add("@" + v.ColumnName, v.value);
                    p.Direction = v.Direction;
                }


        }
        
    }
}
